package teamblue.cardex.components;

import java.util.Date;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class CardexDb {
	
	//CLASS VARIABLES
	private final Context mCtx;
	private static final String CLASS_TAG = "CardexDb";
    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;
	
	//DATABASE LEVEL VARIABLES
	private static final String CARD_DATABASE = "card_database";
    private static final String CARD_TABLE = "card_table";
    private static final int DATABASE_VERSION = 1;
	
    //CARD TABLE VARIABLES
	public static final String CARD_ID = "_id";
	public static final String CARD_ICON = "card_icon";
	public static final String CARD_EXPIRE = "card_expire";
	public static final String CARD_PHONE = "card_phone";
	public static final String CARD_PIN = "card_pin";
    public static final String CARD_NAME = "card_name";
    public static final String CARD_BARCODE = "card_barcode";
    public static final String CARD_FORMAT = "card_format";
    public static final String CARD_NOTE = "card_note";
    public static final String CARD_BALANCE = "card_balance";
    public static final String CARD_URL = "card_url";
    public static final String CARD_EDITABLE = "card_editable";

    //DATABASE CREATION SQL STATEMENT
    private static final String DATABASE_CREATE =
    	"create table if not exists "+CARD_TABLE+" ("+
    	CARD_ID+" integer primary key autoincrement, "+
    	CARD_ICON+" integer, "+
    	CARD_EXPIRE+" integer, "+
		CARD_PIN+" text, "+
		CARD_PHONE+" text, "+
		CARD_NAME+" text not null, "+
		CARD_BARCODE+" text, "+
		CARD_FORMAT+" text, "+
		CARD_NOTE+" text, "+
		CARD_URL+" text, "+
		CARD_EDITABLE+" integer, "+
		CARD_BALANCE+" double);";

    //DATABASE HELPER TO CREATE AND UPDATE DATABASE
    private static class DatabaseHelper extends SQLiteOpenHelper {

        DatabaseHelper(Context context) {
            super(context, CARD_DATABASE, null, DATABASE_VERSION);
        }
        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(DATABASE_CREATE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(CLASS_TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS "+CARD_TABLE);
            onCreate(db);
        }
    }
    
    //DATABASE OBJECT CONSTRUCTOR
    public CardexDb(Context ctx) {
        this.mCtx = ctx;
    }
    
    //OPEN A CONNECTION TO THE CARD DATABASE
    public CardexDb open() throws SQLException {
    	mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();
        return this;
    }
    
    //CLOSE THE DATABASE CONNECTION
    public void close() {
        mDbHelper.close();
    }
    
    //RESET THE CARD DATABASE
    public void reset(){
    	mDbHelper.onUpgrade(this.mDb, 1, 1);
    }


    //ADD A CARD TO THE CARD TABLE, CARD MUST HAVE A NAME
    //RETURNS THE CARD ID ON SUCCESS, -1 ON FAILURE
    public long addCard(Card card) {
    	
        ContentValues initialValues = new ContentValues();
        
        if(card.name == null)return -1;
        initialValues.put(CARD_NAME, card.name);
        if(card.icon == -1) return -1;
        initialValues.put(CARD_ICON, card.icon);
        if(card.expire == null) initialValues.putNull(CARD_EXPIRE);
        else initialValues.put(CARD_EXPIRE, card.expire.getTime());
        if(card.phone == null) initialValues.putNull(CARD_PHONE);
        else initialValues.put(CARD_PHONE, card.phone); 
        if(card.pin == null) initialValues.putNull(CARD_PIN);
        else initialValues.put(CARD_PIN, card.pin); 
        if(card.barcode == null) initialValues.putNull(CARD_BARCODE);
        else initialValues.put(CARD_BARCODE, card.barcode); 
        if(card.format == null) initialValues.putNull(CARD_FORMAT);
        else initialValues.put(CARD_FORMAT, card.format); 
        if(card.note == null) initialValues.putNull(CARD_NOTE);
        else initialValues.put(CARD_NOTE, card.note); 
        if(card.balance == -1) initialValues.putNull(CARD_BALANCE);
        else initialValues.put(CARD_BALANCE, card.balance);
        if(card.url == null) initialValues.putNull(CARD_URL);
        else initialValues.put(CARD_URL, card.url);
        initialValues.put(CARD_EDITABLE, card.editable);
        
        Log.d("values entered", card.name+" "+card.balance);

        return mDb.insert(CARD_TABLE, null, initialValues);
    }

    //REMOVE A CARD FROM THE DATABASE BY ID
    //RETURN TRUE ON SUCCESS, FALSE ON FAILURE
    public boolean removeCard(long card_id) {

        return mDb.delete(CARD_TABLE, CARD_ID + "=" + card_id, null) != 0;
    }

    //RETURN A CURSOR OBJECT OVER ALL CARDS
    public Cursor getAllCards() {

        return mDb.query(CARD_TABLE, new String[] {CARD_ID, CARD_ICON, 
        		CARD_NAME, CARD_BALANCE, CARD_NOTE, CARD_EDITABLE}, null, null, null, null, CARD_NAME);
    }

    //RETURN A CARD OBJECT BY ID
    public Card getCard(long card_id) throws SQLException{
    	
        Cursor mCursor = mDb.query(true, CARD_TABLE, new String[] {CARD_ID,
        		CARD_ICON, CARD_EXPIRE, CARD_PHONE, CARD_PIN, CARD_NAME, CARD_BARCODE,
        		CARD_FORMAT, CARD_NOTE, CARD_BALANCE, CARD_URL, CARD_EDITABLE}, CARD_ID + "=" + card_id, 
        		null, null, null, null, null);
        if (mCursor != null && mCursor.moveToFirst()) {
        	
        	long id = mCursor.getLong(mCursor.getColumnIndex(CARD_ID));
        	String name = mCursor.getString(mCursor.getColumnIndex(CARD_NAME));
        	int icon = mCursor.getInt(mCursor.getColumnIndex(CARD_ICON));
        	Date expire = mCursor.isNull(mCursor.getColumnIndex(CARD_EXPIRE)) ? null 
        			: new Date(mCursor.getLong(mCursor.getColumnIndex(CARD_EXPIRE)));
        	String pin = mCursor.isNull(mCursor.getColumnIndex(CARD_PIN)) ? null
        			: mCursor.getString(mCursor.getColumnIndex(CARD_PIN));
        	String phone = mCursor.isNull(mCursor.getColumnIndex(CARD_PHONE)) ? null 
        			: mCursor.getString(mCursor.getColumnIndex(CARD_PHONE));
        	String barcode = mCursor.isNull(mCursor.getColumnIndex(CARD_BARCODE)) ? null 
        			: mCursor.getString(mCursor.getColumnIndex(CARD_BARCODE));
        	String format = mCursor.isNull(mCursor.getColumnIndex(CARD_FORMAT)) ? null 
        			: mCursor.getString(mCursor.getColumnIndex(CARD_FORMAT));
        	String note = mCursor.isNull(mCursor.getColumnIndex(CARD_NOTE)) ? null 
        			: mCursor.getString(mCursor.getColumnIndex(CARD_NOTE));
        	String url = mCursor.isNull(mCursor.getColumnIndex(CARD_URL)) ? null 
        			: mCursor.getString(mCursor.getColumnIndex(CARD_URL));
        	int editable = mCursor.getInt(mCursor.getColumnIndex(CARD_EDITABLE));
        	double balance = mCursor.isNull(mCursor.getColumnIndex(CARD_BALANCE)) ? -1 
        			: mCursor.getDouble(mCursor.getColumnIndex(CARD_BALANCE));
        	
        	mCursor.close();
        	return new Card(id, icon, expire, pin, phone, name, barcode, format, note, url, editable, balance);
        }
        mCursor.close();
        return null;
    }
    
    //UPDATE A CARD IN THE DATABASE
    public boolean updateCard(Card card) {
    	
    	ContentValues newValues = new ContentValues();
        
    	if(card.name == null)return false;
    	newValues.put(CARD_NAME, card.name);
    	if(card.icon == -1)return false;
    	newValues.put(CARD_ICON, card.icon);
        if(card.expire == null) newValues.putNull(CARD_EXPIRE);
        else newValues.put(CARD_EXPIRE, card.expire.getTime());
        if(card.phone == null) newValues.putNull(CARD_PHONE);
        else newValues.put(CARD_PHONE, card.phone); 
        if(card.pin == null) newValues.putNull(CARD_PIN);
        else newValues.put(CARD_PIN, card.pin); 
        if(card.barcode == null) newValues.putNull(CARD_BARCODE);
        else newValues.put(CARD_BARCODE, card.barcode); 
        if(card.format == null) newValues.putNull(CARD_FORMAT);
        else newValues.put(CARD_FORMAT, card.format); 
        if(card.url == null) newValues.putNull(CARD_URL);
        else newValues.put(CARD_URL, card.url); 
        if(card.note == null) newValues.putNull(CARD_NOTE);
        else newValues.put(CARD_NOTE, card.note); 
        if(card.balance == -1) newValues.putNull(CARD_BALANCE);
        else newValues.put(CARD_BALANCE, card.balance);
        newValues.put(CARD_EDITABLE, card.editable);

        return mDb.update(CARD_TABLE, newValues, CARD_ID + "=" + card.id, null) != 0;
    }
}